import json
import os
import sqlite3
from datetime import datetime, timedelta

# 数据库路径
PATH_TO_DB = 'D:\\project\\python\\DataStreamer\\Upload\\upload.db'


# 单例模式管理数据库连接
class Database:
    _instance = None

    def __new__(cls, *args, **kwargs):
        if not cls._instance:
            cls._instance = super().__new__(cls)
            cls._instance._init_db()
        return cls._instance

    def _init_db(self):
        self.conn = sqlite3.connect(PATH_TO_DB)
        self.conn.execute('PRAGMA journal_mode=WAL;')  # 启用 WAL 模式
        self._create_tables()

    def _create_tables(self):
        cursor = self.conn.cursor()
        # 创建表 upload_links
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS upload_links (
            file_hash TEXT PRIMARY KEY,
            file_name TEXT NOT NULL,
            file_size INTEGER NOT NULL,
            file_chunk INTEGER NOT NULL,
            link_id TEXT NOT NULL,
            expires_at TEXT NOT NULL,
            url_using INTEGER NOT NULL,
            supports_resume INTEGER NOT NULL,
            downloaded_chunks TEXT DEFAULT '[]'
        )
        ''')
        # 创建表 local_files
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS local_files (
            file_hash TEXT PRIMARY KEY,
            file_name TEXT NOT NULL,
            file_exist INTEGER NOT NULL,
            file_size INTEGER NOT NULL,
            file_path TEXT NOT NULL,
            upload_time TEXT NOT NULL
        )
        ''')
        self.conn.commit()

    def get_cursor(self):
        return self.conn.cursor()

    def commit(self):
        self.conn.commit()

    def close(self):
        self.conn.close()


# 初始化数据库连接
db = Database()


def insert_upload_link(file_hash, file_name, file_size, file_chunk, link_id, expires_at, url_using, supports_resume, downloaded_chunks='[]'):
    """插入或更新 upload_links 表"""
    cursor = db.get_cursor()
    cursor.execute('''
    INSERT OR REPLACE INTO upload_links (file_hash, file_name, file_size, file_chunk, link_id, expires_at, url_using, supports_resume, downloaded_chunks)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    ''', (file_hash, file_name, file_size, file_chunk, link_id, expires_at.isoformat(), int(url_using), int(supports_resume), downloaded_chunks))
    db.commit()


def get_upload_link(search_data, search_by='link_id'):
    """根据条件查询 upload_links 表"""
    cursor = db.get_cursor()
    cursor.execute(f'''
    SELECT file_hash, file_name, file_size, file_chunk, link_id, expires_at, url_using, supports_resume, downloaded_chunks
    FROM upload_links
    WHERE {search_by} = ?
    ''', (search_data,))
    result = cursor.fetchone()

    if result:
        return {
            'file_hash': result[0],
            'file_name': result[1],
            'file_size': result[2],
            'file_chunk': result[3],
            'link_id': result[4],
            'expires_at': datetime.fromisoformat(result[5]),
            'url_using': bool(result[6]),
            'supports_resume': bool(result[7]),
            'downloaded_chunks': result[8]
        }
    return None


def update_upload_link(file_hash, file_name=None, file_size=None, file_chunk=None, link_id=None, expires_at=None, url_using=None, supports_resume=None, downloaded_chunks=None):
    """更新 upload_links 表"""
    cursor = db.get_cursor()
    update_fields = []
    update_values = []

    if file_name is not None:
        update_fields.append('file_name = ?')
        update_values.append(file_name)
    if file_size is not None:
        update_fields.append('file_size = ?')
        update_values.append(file_size)
    if file_chunk is not None:
        update_fields.append('file_chunk = ?')
        update_values.append(file_chunk)
    if link_id is not None:
        update_fields.append('link_id = ?')
        update_values.append(link_id)
    if expires_at is not None:
        update_fields.append('expires_at = ?')
        update_values.append(expires_at.isoformat())
    if url_using is not None:
        update_fields.append('url_using = ?')
        update_values.append(int(url_using))
    if supports_resume is not None:
        update_fields.append('supports_resume = ?')
        update_values.append(int(supports_resume))
    if downloaded_chunks is not None:
        update_fields.append('downloaded_chunks = ?')
        update_values.append(downloaded_chunks)

    if not update_fields:
        raise ValueError("至少提供一个字段进行更新")

    update_values.append(file_hash)
    cursor.execute(f'''
    UPDATE upload_links
    SET {', '.join(update_fields)}
    WHERE file_hash = ?
    ''', update_values)
    db.commit()


def delete_upload_link(file_hash):
    """删除 upload_links 表中的记录"""
    cursor = db.get_cursor()
    cursor.execute('''
    DELETE FROM upload_links
    WHERE file_hash = ?
    ''', (file_hash,))
    db.commit()


def insert_local_files(file_hash, file_name, file_exist, file_size, file_path, upload_time):
    """插入或更新 local_files 表"""
    cursor = db.get_cursor()
    cursor.execute('''
    INSERT OR REPLACE INTO local_files (file_hash, file_name, file_exist, file_size, file_path, upload_time)
    VALUES (?, ?, ?, ?, ?, ?)
    ''', (file_hash, file_name, int(file_exist), file_size, file_path, upload_time.isoformat()))
    db.commit()


def get_local_files(search_data, search_by='file_hash'):
    """根据条件查询 local_files 表"""
    cursor = db.get_cursor()
    cursor.execute(f'''
    SELECT file_hash, file_name, file_exist, file_size, file_path, upload_time
    FROM local_files
    WHERE {search_by} = ?
    ''', (search_data,))
    result = cursor.fetchone()

    if result:
        return {
            'file_hash': result[0],
            'file_name': result[1],
            'file_exist': bool(result[2]),
            'file_size': result[3],
            'file_path': result[4],
            'upload_time': datetime.fromisoformat(result[5])
        }
    return None


def update_local_files(file_hash, file_name=None, file_exist=None, file_size=None, file_path=None, upload_time=None):
    """更新 local_files 表"""
    cursor = db.get_cursor()
    update_fields = []
    update_values = []

    if file_name is not None:
        update_fields.append('file_name = ?')
        update_values.append(file_name)
    if file_exist is not None:
        update_fields.append('file_exist = ?')
        update_values.append(int(file_exist))
    if file_size is not None:
        update_fields.append('file_size = ?')
        update_values.append(file_size)
    if file_path is not None:
        update_fields.append('file_path = ?')
        update_values.append(file_path)
    if upload_time is not None:
        update_fields.append('upload_time = ?')
        update_values.append(upload_time.isoformat())

    if not update_fields:
        raise ValueError("至少提供一个字段进行更新")

    update_values.append(file_hash)
    cursor.execute(f'''
    UPDATE local_files
    SET {', '.join(update_fields)}
    WHERE file_hash = ?
    ''', update_values)
    db.commit()


def delete_local_files(file_hash):
    """删除 local_files 表中的记录"""
    cursor = db.get_cursor()
    cursor.execute('''
    DELETE FROM local_files
    WHERE file_hash = ?
    ''', (file_hash,))
    db.commit()


def check_FilesExist(server_files_dir):
    """检查服务器文件是否存在，并更新数据库"""
    cursor = db.get_cursor()
    cursor.execute("SELECT file_hash, file_path FROM local_files")
    db_files = cursor.fetchall()

    server_files = set()
    for root, _, files in os.walk(server_files_dir):
        for file in files:
            server_files.add(file)

    for file_hash, file_path in db_files:
        if file_hash not in server_files:
            upload_link = get_upload_link(file_hash, search_by='file_hash')
            if upload_link and not upload_link['url_using']:
                cursor.execute(
                    "UPDATE local_files SET file_exist = 0 WHERE file_hash = ?",
                    (file_hash,)
                )
                downloaded_chunks = json.dumps([])
                cursor.execute(
                    "UPDATE upload_links SET downloaded_chunks = ? WHERE file_hash = ?",
                    (downloaded_chunks, file_hash)
                )
        else:
            cursor.execute(
                "UPDATE local_files SET file_exist = 1 WHERE file_hash = ?",
                (file_hash,)
            )
    db.commit()


def check_UrlAvailable():
    """检查链接是否过期，并更新数据库"""
    cursor = db.get_cursor()
    now = datetime.now()
    cursor.execute("SELECT file_hash, expires_at FROM upload_links")
    results = cursor.fetchall()
    for file_hash, expires_at_str in results:
        expires_at = datetime.fromisoformat(expires_at_str)
        if now > expires_at:
            cursor.execute(
                "UPDATE upload_links SET url_using = 0 WHERE file_hash = ?",
                (file_hash,)
            )
    db.commit()